package saleMarket.dao;

import lombok.extern.slf4j.Slf4j;
import saleMarket.domain.ShopOwner;
import saleMarket.domain.Warehouse;
import saleMarket.service.ShopOwnerService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

/**
 * @author 张方恒
 * @version 1.0  12-13-2020
 */

@Slf4j
public class WarehouseDao {
    private static WarehouseDao warehouseDao = new WarehouseDao();
    private WarehouseDao() {}
    public static WarehouseDao getInstance() {
        // 静态变量WarehouseDao为空时，才创建WarehouseDao对象。
        // 第二次调用本方法WarehouseDao就不空了，不再创建对象。
        if (warehouseDao == null) {
            warehouseDao = new WarehouseDao();
        }
        return warehouseDao;
    }

    public Collection<Warehouse> findAll(Connection connection) throws SQLException {
        Collection<Warehouse> desiredWarehouses = new ArrayList<>();
        StringBuilder sql_select = new StringBuilder("SELECT*FROM warehouse");
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");

            int shopOwnerid = resultSet_select.getInt("shopOwner_id");
            ShopOwner shopOwner = ShopOwnerService.getInstance().find(shopOwnerid);

            String no = resultSet_select.getString("no");
            String description = resultSet_select.getString("description");

            Warehouse desiredWarehouse = new Warehouse(no,shopOwner,description);
            desiredWarehouse.setId(id);
            desiredWarehouses.add(desiredWarehouse);

        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredWarehouses;
    }

    public Warehouse find(Integer id,Connection connection) throws SQLException {
        String warehouse_sql = "select * from warehouse where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(warehouse_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找Department
        ResultSet resultSet = pstmt.executeQuery();
        Warehouse warehouse =null;
        if(resultSet.next()) {
            //根据id查找结果创建Department对象
            warehouse = new Warehouse(
                    id,
                    resultSet.getString("no"),
                    ShopOwnerService.getInstance().find(resultSet.getInt("shopOwner_id")),
                    resultSet.getString("description"));
        }
        //关闭
        JdbcHelper.close(pstmt, connection);
        return warehouse;
    }

    public boolean update(Warehouse warehouse,Connection connection) throws SQLException {
        String updateWarehouse_sql = "UPDATE warehouse SET shopOwner_id = ?,no = ?," +
                "description = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateWarehouse_sql);
        //为预编译参数赋值
        pstmt.setInt(1, warehouse.getShopOwner().getId());
        pstmt.setString(2, warehouse.getNo());
        pstmt.setString(3, warehouse.getDescription());
        pstmt.setInt(4, warehouse.getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(pstmt);
            return false;
        }else
            JdbcHelper.close(pstmt);
        return  true;
    }

    public boolean add(Warehouse warehouse,Connection connection) throws SQLException {
        String addWarehouse_sql = "INSERT INTO Warehouse (shopOwner_id,no,description)" +
                " VALUES" + "(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addWarehouse_sql);
        //为预编译参数赋值
        pstmt.setInt(1, 4);
        pstmt.setString(2, warehouse.getNo());
        pstmt.setString(3,warehouse.getDescription());

        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(pstmt);
            return false;
        }else
            JdbcHelper.close(pstmt);
        return  true;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {

        String deleteWarehouse_sql = "delete from Warehouse where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteWarehouse_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //获取执行结果
        int affectedRow = pstmt.executeUpdate();
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(pstmt);
            return true;
        }else
            JdbcHelper.close(pstmt);
        return false;
    }

}
